package cn.micro.dc_excel.controller;

import cn.micro.dc_excel.tools.ToolsDaochu;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.text.DateFormat;
import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class DaochuController {
    /**
     * 账户资产>粉丝
     */
    @RequestMapping(value = "fszhzc")
    public void dao4(HttpServletRequest req, HttpServletResponse res, String login_name, String tel, String start, String end) throws Exception {
        String sql = "select * from member_role a "
                + "inner join member b on a.member_id = b.id "
                + "left join tf_integral c on a.member_id=c.member_id "
                + "where a.role_id='f0'";
        if (!StringUtils.isBlank(start)) {
            sql += " and b.create_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and b.create_date <= '" + end + "'";
        }
        if (!StringUtils.isBlank(login_name)) {
            sql += " and b.login_name = '" + login_name + "'";
        }
        if (!StringUtils.isBlank(tel)) {
            sql += " and b.tel = '" + tel + "'";
        }
sql+=" AND a.member_id NOT IN"    
+"(SELECT a1.member_id FROM member_role a1 "
                +" WHERE a1.role_id='f1')"  
                +" AND  a.member_id NOT IN"    
+"(SELECT a2.member_id FROM member_role a2 "
                +" WHERE a2.role_id='f2')"
                +" GROUP BY a.member_id ORDER BY b.create_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "注册时间");
        map.put("0-2", "账号");
        map.put("0-3", "手机号");
        map.put("0-4", "积分数");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("create_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("integral_num"));
        }
        String xlsName = "账户资产>粉丝";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>会员
     */
    @RequestMapping(value = "hyzhzc")
    public void daoa5(HttpServletRequest req, HttpServletResponse res, String login_name, String tel , String start, String end) throws Exception {
        String sql = "SELECT * FROM member_role a" + 
        		"                INNER JOIN member b ON b.id = a.member_id" + 
        		"               LEFT JOIN member_order c ON b.id=c.member_id" + 
        		"               LEFT JOIN tf_integral d ON b.id=d.member_id" + 
        		"              LEFT JOIN tf_gold e ON b.id=e.member_id " + 
        		"              LEFT JOIN tf_coin_rh f ON b.id=f.rh_object " + 
        		"                LEFT JOIN tf_founderapplyfor g ON b.id=g.applyfor_user_id" + 
        		"                WHERE a.role_id='f1'"; 
        if (!StringUtils.isBlank(login_name)) {
            sql += " and b.login_name = '" + login_name + "'";
        }
        if (!StringUtils.isBlank(tel)) {
            sql += " and b.tel = '" + tel + "'";
        }
        if (!StringUtils.isBlank(start)) {
        	String newtime=time3(start);
            sql += " and a.deadline >= '" + newtime + "'";
        }
        if (!StringUtils.isBlank(end)) {
        	String newtime=time3(end);
            sql += " and a.deadline <= '" + newtime + "'";
        }
        sql +=" AND a.member_id NOT IN"    
        +"(SELECT a1.member_id FROM member_role a1" 
                        +" WHERE a1.role_id='f2')";  
        sql+= "  GROUP BY a.member_id ORDER BY a.deadline DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "提交时间");
        map.put("0-2", "通过时间");
        map.put("0-3", "终止时间");
        map.put("0-4", "个人账号");
        map.put("0-5", "姓名");
        map.put("0-6", "手机号");
        map.put("0-7", "身份证号");
        map.put("0-8", "缴纳金额");
        map.put("0-9", "上级推荐号");
        map.put("0-10", "支付状态");
        map.put("0-11", "积分(明细)");
        map.put("0-12", "金币(明细)");
        map.put("0-13", "任和币(明细)");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
            if(((Map) list.get(i)).get("deadline")!=null){
            	String newtime= time2(((Map) list.get(i)).get("deadline"));
            	map.put("" + (i + 1) + "-2", newtime);
            }
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("deadline"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("deadline"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("name"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("idcard_num"));
            map.put("" + (i + 1) + "-8", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-9", ((Map) list.get(i)).get("num"));
            if (((Map) list.get(i)).get("ext1") != null) {
                if (((Map) list.get(i)).get("ext1").equals("1")) {
                    map.put("" + (i + 1) + "-10", "支付成功");
                }
                if (((Map) list.get(i)).get("ext1").equals("0")) {
                    map.put("" + (i + 1) + "-10", "未支付");
                }
            }
            map.put("" + (i + 1) + "-11", ((Map) list.get(i)).get("integral_num"));
            map.put("" + (i + 1) + "-12", ((Map) list.get(i)).get("gold_num"));
            map.put("" + (i + 1) + "-13", ((Map) list.get(i)).get("rh_num"));
        }
        String xlsName = "账户资产>会员";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>创客
     */
    @RequestMapping(value = "ckzhzc")
    public void daoa6(HttpServletRequest req, HttpServletResponse res, String login_name, String tel, String start, String end) throws Exception {
        String sql = "SELECT * FROM member_role a" + 
        		"                INNER JOIN member b ON a.member_id = b.id" + 
        		"                LEFT JOIN member_order c ON a.member_id=c.member_id" + 
        		"                LEFT JOIN tf_integral d ON a.member_id=d.member_id" + 
        		"                LEFT JOIN tf_gold e ON a.member_id=e.member_id" + 
        		"                LEFT JOIN tf_coin_rh f ON a.member_id=f.rh_object" + 
        		"                LEFT JOIN tf_founderapplyfor g ON a.member_id=g.applyfor_user_id" + 
        		"                WHERE a.role_id='f2'AND b.del_flag=0";
        if (!StringUtils.isBlank(login_name)) {
            sql += " and b.login_name = '" + login_name + "'";
        }
        if (!StringUtils.isBlank(tel)) {
            sql += " and b.tel = '" + tel + "'";
        }
        if (!StringUtils.isBlank(start)) {
        	String newtime=time3(start);
            sql += " and a.deadline >= '" + newtime + "'";
        }
        if (!StringUtils.isBlank(end)) {
        	String newtime=time3(end);
            sql += " and a.deadline <= '" + newtime + "'";
        }
        sql+= " GROUP BY a.member_id ORDER BY b.create_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "提交时间");
        map.put("0-2", "通过时间");
        map.put("0-3", "终止时间");
        map.put("0-4", "个人账号");
        map.put("0-5", "姓名");
        map.put("0-6", "手机号");
        map.put("0-7", "缴纳金额");
        map.put("0-8", "创客来源");
        map.put("0-9", "上级推荐号");
        map.put("0-10", "支付状态");
        map.put("0-11", "积分(明细)");
        map.put("0-12", "金币(明细)");
        map.put("0-13", "任和币(明细)");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
            if(((Map) list.get(i)).get("deadline")!=null){
            	String newtime= time2(((Map) list.get(i)).get("deadline"));
            	map.put("" + (i + 1) + "-2", newtime);
            }
            
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("deadline"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-8", ((Map) list.get(i)).get("gratitude"));
            map.put("" + (i + 1) + "-9", ((Map) list.get(i)).get("num"));
            if (((Map) list.get(i)).get("ext1") != null) {
                if (((Map) list.get(i)).get("ext1").equals("1")) {
                    map.put("" + (i + 1) + "-10", "支付成功");
                }
                if (((Map) list.get(i)).get("ext1").equals("0")) {
                    map.put("" + (i + 1) + "-10", "未支付");
                }
            }
            map.put("" + (i + 1) + "-11", ((Map) list.get(i)).get("integral_num"));
            map.put("" + (i + 1) + "-12", ((Map) list.get(i)).get("gold_num"));
            map.put("" + (i + 1) + "-13", ((Map) list.get(i)).get("rh_num"));
        }
        String xlsName = "账户资产>创客";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>商家
     */
    @RequestMapping(value = "sjzhzc")
    public void daoa7(HttpServletRequest req, HttpServletResponse res, String login_name, String tel, String start, String end) throws Exception {
        String sql = "select * from user_role a"
                + " inner join user b on a.user_id = b.id "
                + " inner join tf_coin_rh c on a.user_id=c.rh_object "
                + " where a.role_id='a2' AND b.del_flag=0";
        if (!StringUtils.isBlank(login_name)) {
            sql += " and b.login_name = '" + login_name + "'";
        }
        if (!StringUtils.isBlank(tel)) {
            sql += " and b.tel = '" + tel + "'";
        }
        if (!StringUtils.isBlank(start)) {
            sql += " and b.update_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and b.update_date <= '" + end + "'";
        }
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "注册时间");
        map.put("0-2", "通过时间");
        map.put("0-3", "商家账号");
        map.put("0-4", "商家名称");
        map.put("0-5", "手机号");
        map.put("0-6", "任和币(明细)");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("create_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("update_date"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("name"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("rh_num"));
        }
        String xlsName = "账户资产>商家";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>代理商
     */
    @RequestMapping(value = "dlszhzc")
    public void dao8(HttpServletRequest req, HttpServletResponse res, String login_name, String tel, String district, String start, String end) throws Exception {
        String sql = "select * from user_role a"
                + " inner join user b on a.user_id = b.id "
                + " left join tf_coin_rh c on a.user_id=c.rh_object "
                + " left join user_info d on a.user_id=d.ext1 "
                + " left join tf_founderapplyfor e on a.user_id=e.applyfor_user_id "
                + " where a.role_id='a1'AND b.del_flag=0";
        if (!StringUtils.isBlank(login_name)) {
            sql += " and b.login_name = '" + login_name + "'";
        }
        if (!StringUtils.isBlank(district)) {
            if (district.equals("市级代理")) {
                sql += " and d.district is null";
            } else if (district.equals("区级代理")) {
                sql += " and d.district is not null";
            }
        }
        if (!StringUtils.isBlank(start)) {
            sql += " and b.update_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and b.update_date <= '" + end + "'";
        }
        sql+=" GROUP BY a.user_id";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "代理商账号");
        map.put("0-2", "代理商公司名称");
        map.put("0-3", "代理级别");
        map.put("0-4", "代理区域");
        map.put("0-5", "起始时间");
        map.put("0-6", "终止时间");
        map.put("0-7", "联系人");
        map.put("0-8", "联系方式");
        map.put("0-9", "状态");
        map.put("0-10", "仁和币");
        for (int i = 0; i < list.size(); i++) {
            String sheng = null, shi = null, qu = null;
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("merchant_name"));
            if (((Map) list.get(i)).get("district") == null) {
                map.put("" + (i + 1) + "-3", "市级代理");
            } else {
                map.put("" + (i + 1) + "-3", "区县级代理");
            }
            if (((Map) list.get(i)).get("province") != null) {
                sheng = String.valueOf(((Map) list.get(i)).get("province"));
            }
            if (((Map) list.get(i)).get("city") != null) {
                shi = String.valueOf(((Map) list.get(i)).get("city"));
            }
            if (((Map) list.get(i)).get("district") != null) {
                qu = String.valueOf(((Map) list.get(i)).get("district"));
            }
            if (sheng != null || shi != null || qu != null) {
                String qy = findqy(sheng, shi, qu);
                map.put("" + (i + 1) + "-4", qy);
            }
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("applyfor_date"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("due_date"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("merchant_contact"));
            map.put("" + (i + 1) + "-8", ((Map) list.get(i)).get("tel"));
            if (((Map) list.get(i)).get("rh_status") != null) {
                if (((Map) list.get(i)).get("rh_status").equals("0")) {
                    map.put("" + (i + 1) + "-9", "未锁定");
                } else {
                    map.put("" + (i + 1) + "-9", "锁定");
                }
            }
            map.put("" + (i + 1) + "-10", ((Map) list.get(i)).get("rh_num"));
        }
        String xlsName = "账户资产>代理商";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>积分明细
     */
    @RequestMapping(value = "jfmx")
    public void dao5(HttpServletRequest req, HttpServletResponse res, String id, String start, String end ,String itgd_order) throws Exception {
        String sql = "select * from tf_integral_detail where member_id='" + id + "'";
        if (!StringUtils.isBlank(start)) {
            sql += " and itgd_time >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and itgd_time <= '" + end + "'";
        }
        if (!StringUtils.isBlank(itgd_order)) {
            sql += " and itgd_order = '" + itgd_order + "'";
        }
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "积分收支类型");
        map.put("0-4", "积分数");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("itgd_time"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("itgd_order"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("itgd_type"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("itgd_num"));
        }
        String xlsName = "积分明细";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>金币明细
     */
    @RequestMapping(value = "jbmx")
    public void dao6(HttpServletRequest req, HttpServletResponse res, String id, String start, String end,String gd_order) throws Exception {
        String sql = "select * from tf_gold_detail where member_id='" + id + "'";
        if (!StringUtils.isBlank(start)) {
            sql += " and itgd_time >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and itgd_time <= '" + end + "'";
        }
        if (!StringUtils.isBlank(gd_order)) {
            sql += " and gd_order = '" + gd_order + "'";
        }
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "金币收支类型");
        map.put("0-4", "金币数");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("gd_time"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("gd_order"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("gd_type"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("gd_goldnum"));
        }
        String xlsName = "金币明细";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 账户资产>仁和币明细==
     */
    @RequestMapping(value = "rhbmx")
    public void dao7(HttpServletRequest req, HttpServletResponse res, String id, String start, String end,String orderId) throws Exception {
        String sql = "select * from tf_coin_variation where rh_object='" + id + "'";
        if (!StringUtils.isBlank(start)) {
            sql += " and rh_crawlTime >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and rh_crawlTime <= '" + end + "'";
        }
        if (!StringUtils.isBlank(orderId)) {
            sql += " and orderId = '" + orderId + "'";
        }
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "仁和币收支类型");
        map.put("0-4", "仁和币数");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("rh_crawlTime"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("rh_orderId"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("rh_crawExplain"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("rh_crawlNum"));
        }
        String xlsName = "仁和币明细";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 创客管理(待审核)
     */
    @RequestMapping(value = "ckgldzf")
    public void dao9(HttpServletRequest req, HttpServletResponse res, String true_name, String start, String end) throws Exception {
        String sql = "SELECT * FROM tf_founderapplyfor g "
    + " LEFT JOIN member_role a ON a.member_id = g.applyfor_user_id" 
    + " LEFT JOIN member b ON b.id = g.applyfor_user_id" 
    + " LEFT JOIN member_order c ON g.applyfor_user_id =c.member_id"
    + " WHERE g.applyfor_type=1" 
    + " AND g.audit_status=0";
        if (!StringUtils.isBlank(start)) {
            sql += " and g.applyfor_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and g.applyfor_date <= '" + end + "'";
        }
        if (!StringUtils.isBlank(true_name)) {
            sql += " and g.true_name = '" + true_name + "'";
        }
        sql+=" GROUP BY g.applyfor_user_id ORDER BY b.create_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "提交时间");
        map.put("0-2", "个人账号");
        map.put("0-3", "姓名");
        map.put("0-4", "手机号");
        map.put("0-5", "身份证号");
        map.put("0-6", "缴纳金额");
        map.put("0-7", "支付状态");
        map.put("0-8", "状态");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("idcard_num"));
            //map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-6", "399");
            if (((Map) list.get(i)).get("ext1") != null) {
                if (((Map) list.get(i)).get("ext1").equals("1")) {
                    map.put("" + (i + 1) + "-7", "支付成功");
                }
                if (((Map) list.get(i)).get("ext1").equals("0")) {
                    map.put("" + (i + 1) + "-7", "未支付");
                }
            }
            map.put("" + (i + 1) + "-8", "待审核");
        }
        String xlsName = "创客管理(待审核)";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 创客管理(已通过)
     */
    @RequestMapping(value = "ckglytg")
    public void dao10(HttpServletRequest req, HttpServletResponse res, String loginName, String shutdown) throws Exception {
         String sql = "SELECT * FROM member_role a" + 
         		"                INNER JOIN member b ON a.member_id = b.id" + 
         		"                LEFT JOIN tf_founderapplyfor g ON a.member_id=g.applyfor_user_id" +
         		"                LEFT JOIN member_order c ON a.member_id =c.member_id"+
         		"                WHERE a.role_id='f2'AND b.del_flag=0";    		   
        if (!StringUtils.isBlank(loginName)) {
            sql += " and b.login_name = '" + loginName + "'";
        }
        sql += " GROUP BY a.member_id"
	    +" ORDER BY b.create_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "申请时间");
        
        map.put("0-3", "个人账号");
        map.put("0-2", "姓名");
        map.put("0-4", "手机号");
        map.put("0-5", "身份证号");
        map.put("0-6", "缴纳金额");
        map.put("0-7", "状态");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("idcard_num"));
            //map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-6", "399");
            map.put("" + (i + 1) + "-7", "已通过");
        }
        String xlsName = "创客管理(已通过)";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 创客管理(已驳回)
     */
    @RequestMapping(value = "ckglybh")
    public void dao11(HttpServletRequest req, HttpServletResponse res, String true_name, String start, String end) throws Exception {
    	 String sql = "SELECT * FROM tf_founderapplyfor g "
         	    + " LEFT JOIN member_role a ON a.member_id = g.applyfor_user_id" 
         	    + " LEFT JOIN member b ON b.id = g.applyfor_user_id" 
         	    + " LEFT JOIN member_order c ON g.applyfor_user_id =c.member_id"
         	    + " WHERE g.applyfor_type=1" 
         	    + " AND g.audit_status='-1'";
        if (!StringUtils.isBlank(start)) {
            sql += " and g.applyfor_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and g.applyfor_date <= '" + end + "'";
        }
        if (!StringUtils.isBlank(true_name)) {
            sql += " and g.true_name = '" + true_name + "'";
        }
        sql += " GROUP BY g.applyfor_user_id ORDER BY b.create_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "申请时间");
        map.put("0-2", "姓名");
        map.put("0-3", "个人账号");
        map.put("0-4", "手机号");
        map.put("0-5", "身份证号");
        map.put("0-6", "缴纳金额");
       
        map.put("0-7", "支付状态");
        map.put("0-8", "状态");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("idcard_num"));
            //map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-6", "399");
            if (((Map) list.get(i)).get("ext1") != null) {
                if (((Map) list.get(i)).get("ext1").equals("1")) {
                    map.put("" + (i + 1) + "-7", "支付成功");
                }
                if (((Map) list.get(i)).get("ext1").equals("0")) {
                    map.put("" + (i + 1) + "-7", "未支付");
                }
            }
            map.put("" + (i + 1) + "-8", "已驳回");
        }
        String xlsName = "创客管理(已驳回)";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 会员管理(待审核)
     */
    @RequestMapping(value = "hygldzf")
    public void dao12(HttpServletRequest req, HttpServletResponse res, String true_name, String start, String end ,String ext1) throws Exception {
    	 String sql = "SELECT * FROM tf_founderapplyfor g "
    			    + " LEFT JOIN member_role a ON a.member_id = g.applyfor_user_id" 
    			    + " LEFT JOIN member b ON b.id = g.applyfor_user_id" 
    			    + " LEFT JOIN member_order c ON g.applyfor_user_id =c.member_id"
    			    + " WHERE g.applyfor_type=0" 
    			    + " AND g.audit_status=0";
        if (!StringUtils.isBlank(start)) {
            sql += " and g.applyfor_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and g.applyfor_date <= '" + end + "'";
        }
        if (!StringUtils.isBlank(true_name)) {
            sql += " and g.true_name = '" + true_name + "'";
        }
        if (!StringUtils.isBlank(ext1)) {
            sql += " and b.ext1 = '" + ext1 + "'";
        }
        sql+=" ORDER BY g.`applyfor_date` DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "申请时间");
        
        map.put("0-2", "姓名");
        map.put("0-3", "手机号");
        map.put("0-4", "身份证号");
        map.put("0-5", "缴纳金额");
        
        map.put("0-6", "支付状态");
        map.put("0-7", "状态");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
           
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("idcard_num"));
            //map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-5", "199");
            if (((Map) list.get(i)).get("ext1") != null) {
                if (((Map) list.get(i)).get("ext1").equals("1")) {
                    map.put("" + (i + 1) + "-6", "支付成功");
                }
                if (((Map) list.get(i)).get("ext1").equals("0")) {
                    map.put("" + (i + 1) + "-6", "未支付");
                }
            }
            map.put("" + (i + 1) + "-7", "待审核");
        }
        String xlsName = "会员管理(待审核)";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 会员管理(已通过)
     */
    @RequestMapping(value = "hyglytg")
    public void dao13(HttpServletRequest req, HttpServletResponse res, String true_name, String start, String end ,String ext1) throws Exception {
    	 String sql = "   SELECT * FROM member_role a" + 
    	 		"                INNER JOIN member b ON b.id = a.member_id" + 
    	 		"                LEFT JOIN member_order c ON a.member_id =c.member_id"+
    	 		"                LEFT JOIN tf_founderapplyfor g ON b.id=g.applyfor_user_id" + 
    	 		"                WHERE a.role_id='f1'AND b.del_flag=0";     		   
        if (!StringUtils.isBlank(start)) {
            sql += " and g.applyfor_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and g.applyfor_date <= '" + end + "'";
        }
        if (!StringUtils.isBlank(true_name)) {
            sql += " and g.true_name = '" + true_name + "'";
        }
        if (!StringUtils.isBlank(ext1)) {
            sql += " and b.ext1 = '" + ext1 + "'";
        }
        sql += " GROUP BY a.member_id"
        	    +" ORDER BY g.`applyfor_date` DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "提交时间");
       
        map.put("0-3", "个人账号");
        map.put("0-2", "姓名");
        map.put("0-4", "手机号");
        map.put("0-5", "身份证号");
        map.put("0-6", "缴纳金额");
       
        map.put("0-7", "状态");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
          
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("mobile"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("identity_Number"));
            //map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-6", "199");
            map.put("" + (i + 1) + "-7", "已通过");
        }
        String xlsName = "会员管理(已通过)";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    /**
     * 会员管理(已驳回)
     */
    @RequestMapping(value = "hyglybh")
    public void dao14(HttpServletRequest req, HttpServletResponse res, String true_name, String start, String end ,String ext1) throws Exception {
        String sql = "SELECT * FROM tf_founderapplyfor g LEFT JOIN member b ON g.applyfor_user_id=b.id "+
        		"                LEFT JOIN member_order c ON g.applyfor_user_id =c.member_id"
                +" WHERE g.applyfor_type=0"
                +" AND g.audit_status='-1'";
        if (!StringUtils.isBlank(start)) {
            sql += " and g.applyfor_date >= '" + start + "'";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and g.applyfor_date <= '" + end + "'";
        }
        if (!StringUtils.isBlank(true_name)) {
            sql += " and g.true_name = '" + true_name + "'";
        }
        sql += " GROUP BY g.applyfor_user_id"
        	    +" ORDER BY g.`applyfor_date` DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "申请时间");
        map.put("0-2", "姓名");
        map.put("0-3", "手机号");
        map.put("0-4", "身份证号");
        map.put("0-5", "缴纳金额");
       
        map.put("0-6", "支付状态");
        map.put("0-7", "状态");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("applyfor_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("tel"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("idcard_num"));
            //map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("ext2"));
            map.put("" + (i + 1) + "-5", "199");
            
            if (((Map) list.get(i)).get("ext1") != null) {
                if (((Map) list.get(i)).get("ext1").equals("1")) {
                    map.put("" + (i + 1) + "-6", "支付成功");
                }
                if (((Map) list.get(i)).get("ext1").equals("0")) {
                    map.put("" + (i + 1) + "-6", "未支付");
                }
            }
            map.put("" + (i + 1) + "-7", "已驳回");
        }
        String xlsName = "会员管理(已驳回)";
        ToolsDaochu.getTfAttenceListExport(req, res, xlsName, map);
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List daochu(String sql) {
        List list = jdbcTemplate.queryForList(sql);
        return list;

    }

    public String findqy(String sheng, String shi, String qu) {
        String sq = "SELECT c.local_name sheng,b.local_name shi,d.local_name  qu FROM user_info a,regions b,regions c,regions d WHERE a.city = b.region_id AND a.province = c.region_id AND a.district = d.region_id";
        if (!StringUtils.isBlank(sheng)) {
            sq += " AND c.region_id = '" + sheng + "'";
        }
        if (!StringUtils.isBlank(shi)) {
            sq += " AND a.city = '" + shi + "'";
        }
        if (!StringUtils.isBlank(qu)) {
            sq += " AND d.region_id = '" + qu + "'";
        }
        List list = jdbcTemplate.queryForList(sq);
        String qy = "";
        if (!StringUtils.isBlank(sheng)) {
            qy += String.valueOf(((Map) list.get(0)).get("sheng"));
        }
        if (!StringUtils.isBlank(shi)) {
            qy += String.valueOf(((Map) list.get(0)).get("shi"));
        }
        if (!StringUtils.isBlank(qu)) {
            qy += String.valueOf(((Map) list.get(0)).get("qu"));
        }
        try {
            String s = new String((qy).getBytes("gb2312"), "iso-8859-1");
        } catch (UnsupportedEncodingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return qy;
    }
    //date减1年
    public static String time2(Object time){
		Format f = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Calendar c = Calendar.getInstance();
        c.setTime((Date) time);
        c.add(Calendar.YEAR, -1);
       String newtime= f.format(c.getTime());
	   return newtime;     
	}
    public static String time3(String time){
		Format f = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Calendar c = Calendar.getInstance();
        Date date;
		try {
			date = format1.parse(time);
			 c.setTime(date);
			 c.add(Calendar.YEAR, +1);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}    
		String newtime= f.format(c.getTime());
		return newtime;
}
}
